﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;
using System.Web.Configuration;

namespace ReconUI
{
    public class ReconcileService
    {
        
        String matchType;
        MySqlConnection connection;// = new MySqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
        MySqlCommand cmd;
        UserVO objUserVO;

        MySqlDataAdapter adapter;
        bool IsFirtRow = true;
        
        public ReconcileService()
        {
            connection = new MySqlConnection(WebConfigurationManager.ConnectionStrings["mySQLConnection"].ToString());
        }
        public void ReconcileByExactAmount(DataTable dtInvoiceUnMatch, DataTable dtStatementUnMatch)
        {
            
            for (int invCount = 0; invCount < dtInvoiceUnMatch.Rows.Count; invCount++)
            {
                double invoiceAmt = Convert.ToDouble(dtInvoiceUnMatch.Rows[invCount]["amount"]);
                for (int stmCount = 0; stmCount < dtStatementUnMatch.Rows.Count; stmCount++)
                {
                    double stmntAmt = Convert.ToDouble(dtStatementUnMatch.Rows[stmCount]["amount"]);

                    if (invoiceAmt == stmntAmt)
                    {
                        matchType = "Amount";
                        DataRow matchedInvoiceRow = dtInvoiceUnMatch.Rows[invCount];
                        DataRow matchedStmtRow = dtStatementUnMatch.Rows[stmCount];
                        insertMatchedInvoice(matchedInvoiceRow, matchedStmtRow);
                        //Logic to re bind data table in grid view
                        /*
                        string retVal =  insertMatchedInvoice(matchedInvoiceRow, matchedStmtRow);

                        if (retVal != "")
                        {
                            dtInvoiceUnMatch.Rows[invCount].Delete();
                            dtInvoiceUnMatch.Rows[invCount].AcceptChanges();
                            dtStatementUnMatch.Rows[stmCount].Delete();
                            dtStatementUnMatch.Rows[stmCount].AcceptChanges();

                            HttpContext.Current.Session["UnMatchedInvoiceList"] = dtInvoiceUnMatch;
                            HttpContext.Current.Session["UnMatchedStatementList"] = dtStatementUnMatch;
                        }*/
                        //Update status & other flags in Invoice as well as in Statement Table.
                    }
                }
            }

            /* //Success for invoice table matched row
            var qry1 = dtInvoiceUnMatch.AsEnumerable().Select(a => new { amount = a["amount"].ToString() });
            var qry2 = dtStatementUnMatch.AsEnumerable().Select(b => new { amount = b["amount"].ToString() });

            var exceptAB = qry1.Intersect(qry2);
            

            DataTable dtMisMatch = (from a in dtInvoiceUnMatch.AsEnumerable()
                                    join ab in exceptAB  on a["amount"].ToString() equals ab.amount
                                    select a).CopyToDataTable(); */

            //DataTable dtMisMatch = new DataTable();
            //dtMisMatch.TableName = "ExactAmountMatchTable";
            
            //foreach (DataRow items in dtInvoiceUnMatch.Rows)
            //{

            //    DataRow[] matchedInvoiceRow = dtStatementUnMatch.Select("amount =" + items["amount"] );
            //    foreach (DataRow dr in matchedInvoiceRow)
            //    {
            //        dtMisMatch.Rows.Add(matchedInvoiceRow);
            //    }
            //}

        }       
        public void ReconcileByAmountAndRef(DataTable dtInvoiceUnMatch, DataTable dtStatementUnMatch)
        {
            for (int invCount = 0; invCount < dtInvoiceUnMatch.Rows.Count; invCount++)
            {
                double invoiceAmt = Convert.ToDouble(dtInvoiceUnMatch.Rows[invCount]["amount"]);
                String InvoiceNumber = (dtInvoiceUnMatch.Rows[invCount]["invoice_number"]).ToString();
                for (int stmCount = 0; stmCount < dtStatementUnMatch.Rows.Count; stmCount++)
                {
                    double stmntAmt = Convert.ToDouble(dtStatementUnMatch.Rows[stmCount]["amount"]);
                    String stmtInvoiceNumber = (dtStatementUnMatch.Rows[stmCount]["ref_no_or_cheque_no"]).ToString();
                    if (invoiceAmt == stmntAmt && (InvoiceNumber == stmtInvoiceNumber))
                    {
                        matchType = "Amount&Ref";
                        DataRow matchedInvoiceRow = dtInvoiceUnMatch.Rows[invCount];
                        DataRow matchedStmtRow = dtStatementUnMatch.Rows[stmCount];
                        insertMatchedInvoice(matchedInvoiceRow, matchedStmtRow);
                        //Logic to re bind data table in grid view
                        /*
                        string retVal = insertMatchedInvoice(matchedInvoiceRow, matchedStmtRow);
                        if (retVal != "")
                        {
                            dtInvoiceUnMatch.Rows[invCount].Delete();
                            dtInvoiceUnMatch.Rows[invCount].AcceptChanges();
                            dtStatementUnMatch.Rows[stmCount].Delete();
                            dtStatementUnMatch.Rows[stmCount].AcceptChanges();

                            HttpContext.Current.Session["UnMatchedInvoiceList"] = dtInvoiceUnMatch;
                            HttpContext.Current.Session["UnMatchedStatementList"] = dtStatementUnMatch;
                        }*/
                        //Update status & other flags in Invoice as well as in Statement Table.
                    }
                }
            }
        }

        public void ReconcileByDiscount(DataTable dtInvoiceUnMatch, DataTable dtStatementUnMatch)
        {

            /*//If Remitter id is same for file then uncomment this logic
             * string remId = Convert.ToString(dtInvoiceUnMatch.Rows[0]["remitter_id"]);
             *  DataSet ds = new DataSet();
             * DateTime effectiveDate;
               double discountPerc = 0.00;
               string strQuery = "";
               double discountAmount = 0.00;
            try
            {
                connection.Open();
             * strQuery = "SELECT effective_date,discount_percentage FROM mast_discount_maint where discount_code in (SELECT discount_code FROM mast_remitter_maint where customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId +"' and remitter_id ='" + remId + "') and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'";
             * cmd = new MySqlCommand(strQuery, connection);
                adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(ds, "DiscountDetail");
            }
            catch (Exception exc)
            {

            }
            finally
            {
                connection.Close();
            }

             if (ds.Tables[0].Rows.count > 0)
             {
              effectiveDate = ds.Tables[0].Rows[0][0].;
              discountPerc = ds.Tables[0].Rows[0][1].;
             }
            
            discountAmount = invoiceAmt * (discountPerc / 100);
             */

            for (int invCount = 0; invCount < dtInvoiceUnMatch.Rows.Count; invCount++)
            {
                double invoiceAmt = Convert.ToDouble(dtInvoiceUnMatch.Rows[invCount]["amount"]);
                string remId = Convert.ToString(dtInvoiceUnMatch.Rows[invCount]["remitter_id"]);
                DateTime invoiceDate = Convert.ToDateTime(dtInvoiceUnMatch.Rows[invCount]["date"]);
                
                for (int stmCount = 0; stmCount < dtStatementUnMatch.Rows.Count; stmCount++)
                {
                    double stmntAmt = Convert.ToDouble(dtStatementUnMatch.Rows[stmCount]["amount"]);
                    DateTime statementDate = Convert.ToDateTime(dtStatementUnMatch.Rows[stmCount]["value_date"]); ;

                    DateTime effectiveDate;
                    double discountPerc = 0.00;
                    string strQuery = "";
                    double discountAmount = 0.00;
                    int dayStart = 0;
                    int dayEnd = 0;

                    
                    connection.Open();


                    strQuery = "SELECT effective_date,discount_percentage,days_start,days_end FROM mast_discount_maint where discount_code in (SELECT discount_code FROM mast_remitter_maint where customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "' and remitter_id ='" + remId + "') and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'";
                    cmd = new MySqlCommand(strQuery, connection);
                    MySqlDataReader drDiscountcode = cmd.ExecuteReader();
                    if (drDiscountcode.HasRows)
                    {
                        while (drDiscountcode.Read())
                        {
                            effectiveDate = drDiscountcode.GetDateTime(0);
                          
                            dayStart = drDiscountcode.GetInt32(2);
                            dayEnd = drDiscountcode.GetInt32(3);
                            if ((invoiceDate <= statementDate) && (statementDate <= invoiceDate.AddDays(dayEnd)))
                            {
                                //find discount / penality and store in variable
                                discountPerc = drDiscountcode.GetDouble(1);
                                break;
                            }

                        }
                    }

                    drDiscountcode.Close();
                    drDiscountcode.Dispose();
                    connection.Close();

                    if (discountPerc > 0.0)
                    {

                        discountAmount = invoiceAmt * (discountPerc / 100);
                        if (((invoiceAmt - (discountAmount)) == stmntAmt))
                        {
                            matchType = "Discount";
                            DataRow matchedInvoiceRow = dtInvoiceUnMatch.Rows[invCount];
                            DataRow matchedStmtRow = dtStatementUnMatch.Rows[stmCount];
                            insertMatchedInvoice(matchedInvoiceRow, matchedStmtRow);

                        }
                    }
                }
            }
        }

        public void ReconcileByPenalty(DataTable dtInvoiceUnMatch, DataTable dtStatementUnMatch)
        {
            for (int invCount = 0; invCount < dtInvoiceUnMatch.Rows.Count; invCount++)
            {
                double invoiceAmt = Convert.ToDouble(dtInvoiceUnMatch.Rows[invCount]["amount"]);
                string remId = Convert.ToString(dtInvoiceUnMatch.Rows[invCount]["remitter_id"]);
                DateTime invoiceDate = Convert.ToDateTime(dtInvoiceUnMatch.Rows[invCount]["date"]);

                for (int stmCount = 0; stmCount < dtStatementUnMatch.Rows.Count; stmCount++)
                {
                    double stmntAmt = Convert.ToDouble(dtStatementUnMatch.Rows[stmCount]["amount"]);
                    DateTime statementDate = Convert.ToDateTime(dtStatementUnMatch.Rows[stmCount]["value_date"]); ;

                    DateTime effectiveDate;
                    double penaltyPerc = 0.00;
                    string strQuery = "";
                    double penaltyAmount = 0.00;
                    int dayStart = 0;
                    int dayEnd = 0;


                    connection.Open();


                    strQuery = "SELECT effective_date,panalty_percentage,days_start,days_end FROM mast_panalty_maint where panalty_code in (SELECT panalty_code FROM mast_remitter_maint where customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "' and remitter_id ='" + remId + "') and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'";
                    cmd = new MySqlCommand(strQuery, connection);
                    MySqlDataReader drDiscountcode = cmd.ExecuteReader();
                    if (drDiscountcode.HasRows)
                    {
                        while (drDiscountcode.Read())
                        {
                            effectiveDate = drDiscountcode.GetDateTime(0);

                            dayStart = drDiscountcode.GetInt32(2);
                            dayEnd = drDiscountcode.GetInt32(3);
                            if ((invoiceDate <= statementDate) && (statementDate <= invoiceDate.AddDays(dayEnd)))
                            {
                                //For Penalty Condition would be as below
                                //////if (statementDate > invoiceDate.AddDays(dayEnd))
                                //////{
                                //find discount / penality and store in variable
                                penaltyPerc = drDiscountcode.GetDouble(1);
                                break;
                            }

                        }
                    }

                    drDiscountcode.Close();
                    drDiscountcode.Dispose();
                    connection.Close();

                    if (penaltyPerc  > 0.0)
                    {

                        penaltyAmount  = invoiceAmt * (penaltyPerc  / 100);
                        if (((invoiceAmt + (penaltyAmount)) == stmntAmt))
                        {
                            matchType = "Penalty";
                            DataRow matchedInvoiceRow = dtInvoiceUnMatch.Rows[invCount];
                            DataRow matchedStmtRow = dtStatementUnMatch.Rows[stmCount];
                            insertMatchedInvoice(matchedInvoiceRow, matchedStmtRow);

                        }
                    }
                }
            }
        }
        //Logic to re bind data table in grid view
        /*private string insertMatchedInvoice(DataRow invoiceRow,DataRow stmtRow)*/
        private void insertMatchedInvoice(DataRow invoiceRow,DataRow stmtRow)
        {
            //Logic to re bind data table in grid view
            /*string returnValue = "";*/
            MySqlTransaction mySqlTran;            
            
            //if (IsFirtRow)
            //{
                connection.Open();
            //}               
                mySqlTran = connection.BeginTransaction(IsolationLevel.Serializable);
                try
                {
                    DataTable dtTemp;

                    String schemaString = "";
                    schemaString = "SELECT customer_id,invoice_unique_id,statement_unique_id,invoice_amount,statement_amount,match_by,match_date,auth_by,auth_date,remitter_user,remitter_date,match_type FROM pro_matching where invoice_unique_id = 0";
                    dtTemp = getMatchingDataTable();

                    DataRow dr = dtTemp.NewRow();
                    
                    dr[0] = ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId;
                    dr[1] = invoiceRow["invoice_unique_id"].ToString();
                    dr[2] = stmtRow["statement_unique_id"].ToString();
                    dr[3] = Double.Parse(invoiceRow["amount"].ToString());
                    dr[4] = Double.Parse(stmtRow["amount"].ToString());
                    dr[5] = ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).userId;
                    dr[6] = DateTime.Parse(System.DateTime.Now.ToString("yyyy-MM-dd"));
                    dr[7] = ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).userId;
                    dr[8] = DateTime.Parse(System.DateTime.Now.ToString("yyyy-MM-dd"));
                    dr[9] = ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).userId;
                    dr[10] = DateTime.Parse(System.DateTime.Now.ToString("yyyy-MM-dd"));
                    dr[11] = matchType;

                    dtTemp.Rows.Add(dr);
                    
                    //connection.Open();
                    string strQueryInvoice = "update pro_invoice set invoic_status = 'M',is_partial = 0 where invoice_unique_id = '" + invoiceRow["invoice_unique_id"].ToString() + "' and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'";
                    cmd = new MySqlCommand(strQueryInvoice, connection, mySqlTran);
                    int retval = cmd.ExecuteNonQuery();

                    string strQueryStatement = "update pro_statement set statement_status = 'M',is_partial = 0 where statement_unique_id = '" + stmtRow["statement_unique_id"].ToString() + "' and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'";
                    cmd = new MySqlCommand(strQueryStatement, connection, mySqlTran);
                    retval = cmd.ExecuteNonQuery();

                    cmd = new MySqlCommand(schemaString, connection, mySqlTran);

                    MySqlDataAdapter adpMatchingRow = new MySqlDataAdapter(cmd);
                    MySqlCommandBuilder cmdBuilder;
                    adpMatchingRow.UpdateBatchSize = 10;

                    if (dtTemp != null)
                    {
                        if (dtTemp.Rows.Count != 0)
                        {
                            // Update the DataSet with the Database
                            cmdBuilder = new MySqlCommandBuilder(adpMatchingRow);
                            adpMatchingRow.Update(dtTemp);//Update database table with file record.
                        }
                    }
                    mySqlTran.Commit();
                    //Logic to re bind data table in grid view
                    /*return returnValue = "Success";*/
                }
                catch (Exception ex)
                {
                    mySqlTran.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                    IsFirtRow = false;                    
                }
            
        }

        public static DataTable getMatchingDataTable()
        {
            DataTable dtStaggingToInvoice = new DataTable();
            dtStaggingToInvoice.Columns.Add("customer_id", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("invoice_unique_id", typeof(System.Decimal));
            dtStaggingToInvoice.Columns.Add("statement_unique_id", typeof(System.Decimal));
            dtStaggingToInvoice.Columns.Add("invoice_amount", typeof(System.Double));            
            dtStaggingToInvoice.Columns.Add("statement_amount", typeof(System.Double));

            dtStaggingToInvoice.Columns.Add("match_by", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("match_date", typeof(System.DateTime));
            
            dtStaggingToInvoice.Columns.Add("auth_by", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("auth_date", typeof(System.DateTime));
            dtStaggingToInvoice.Columns.Add("remitter_user", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("remitter_date", typeof(System.DateTime));
            dtStaggingToInvoice.Columns.Add("match_type", typeof(System.String));
            return dtStaggingToInvoice;
            
        }

        public double getDiscountDetails(string customerId, string remitterId, DateTime invoiceDate, DateTime statementDate)
        {
            connection.Open();
            DateTime effectiveDate;
            string strQuery = "";
            int dayStart = 0;
            int dayEnd = 0;
            double discountPerc = 0.00;
            strQuery = "SELECT effective_date,discount_percentage,days_start,days_end FROM mast_discount_maint where discount_code in (SELECT discount_code FROM mast_remitter_maint where customer_id = '" + customerId + "' and remitter_id ='" + remitterId + "') and customer_id = '" + ((UserVO)HttpContext.Current.Session["CurrentUserVO"]).customerId + "'";
            cmd = new MySqlCommand(strQuery, connection);
            MySqlDataReader drDiscountcode = cmd.ExecuteReader();
            if (drDiscountcode.HasRows)
            {
                while (drDiscountcode.Read())
                {
                    effectiveDate = drDiscountcode.GetDateTime(0);

                    dayStart = drDiscountcode.GetInt32(2);
                    dayEnd = drDiscountcode.GetInt32(3);
                    if ((invoiceDate <= statementDate) && (statementDate <= invoiceDate.AddDays(dayEnd)))
                    {
                        //find discount / penality and store in variable
                        discountPerc = drDiscountcode.GetDouble(1);
                        break;
                    }

                }
            }

            drDiscountcode.Close();
            drDiscountcode.Dispose();
            connection.Close();
            return discountPerc;
        }

        public double getPenaltyDetails(string customerId, string remitterId, DateTime invoiceDate, DateTime statementDate)
        {
            connection.Open();
            DateTime effectiveDate;
            string strQuery = "";
            int dayStart = 0;
            int dayEnd = 0;
            double penaltyPerc = 0.00;
            strQuery = "SELECT effective_date,panalty_percentage,days_start,days_end FROM mast_panalty_maint where panalty_code in (SELECT panalty_code FROM mast_remitter_maint where customer_id =  '" + customerId + "' and remitter_id ='" + remitterId + "') and customer_id =  '" + customerId + "'";
            cmd = new MySqlCommand(strQuery, connection);
            MySqlDataReader drDiscountcode = cmd.ExecuteReader();
            if (drDiscountcode.HasRows)
            {
                while (drDiscountcode.Read())
                {
                    effectiveDate = drDiscountcode.GetDateTime(0);

                    dayStart = drDiscountcode.GetInt32(2);
                    dayEnd = drDiscountcode.GetInt32(3);
                    if ((invoiceDate <= statementDate) && (statementDate <= invoiceDate.AddDays(dayEnd)))
                    {
                        //find discount / penality and store in variable
                        penaltyPerc = drDiscountcode.GetDouble(1);
                        break;
                    }

                }
            }

            drDiscountcode.Close();
            drDiscountcode.Dispose();
            connection.Close();
            return penaltyPerc;     
        }
        

    }
}